import requests
import csv
import pymysql


def main():
    # 获取网页请求
    try:
        resp = requests.get(url, headers=headers)
    except requests.exceptions.ConnectionError:
        print('ERROR，网络连接错误！！！')
        return
    print(resp.url)

    # 判断响应的网页是否正确
    if url not in resp.url:
        print('网页异常跳转')
        return

    data = resp.json()
    city_data = data['data']['cityByLetter']
    get_cityCode(city_data)

    # 将数据以csv格式存储
    with open('city_code.csv', 'w', newline='') as f:
        csv_writer = csv.writer(f)
        csv_writer.writerows(city_code)

    # 将数据存储到mysql中
    sql2 = 'show tables'
    cursor.execute(sql2)
    result = cursor.fetchall()
    tables = []
    for i in result:
        tables.append(i[0])
    if 'city_code' not in tables:
        sql3 = 'create table city_code(code varchar(10) not null, name varchar(20) not null)'
        cursor.execute(sql3)
        db.commit()

    try:
        cursor.executemany(sql, tuple(city_code))
        db.commit()
    except:
        print('数据插入失败')
        db.rollback()

    print('数据写入成功，城市数量：', len(city_code))


# 对请求到的数据解析
def get_cityCode(data):
    for i in data:
        data1 = data[i]
        for j in data1:
            print(j)
            li = (j['adcode'], j['name'])
            city_code.append(li)


if __name__ == '__main__':
    url = 'https://www.amap.com/service/cityList'
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) '
                      'Chrome/90.0.4430.72 Safari/537.36',
    }
    city_code = []
    db = pymysql.connect(host='localhost', user='root', password='123456', database='city_weather')
    cursor = db.cursor()
    sql = "INSERT INTO CITY_CODE(CODE, NAME) VALUES (%s, %s)"
    main()
    db.close()

